﻿use master
go

if exists(select * from sys.databases where name='Student')
drop database Student

create database Student
on
(
name='Student',
filename='D:\SQLcunchu\Student.mdf',
size=5MB,
maxsize=100MB,
filegrowth=10%
)
log on
(
name='Student_log',
filename='D:\SQLcunchu\Student.ldf',
size=5MB,
maxsize=100MB,
filegrowth=10%
)
go
use Student
go
create table Class
(
ClassID int primary key identity(1,1),
ClassName nvarchar(20) unique not null
)
create table Student
(
StuID int primary key identity(1,1),
ClassID int references Class(ClassID),
StuName nvarchar(20) not null,
StuSex nvarchar(1) check(StuSex in ('男','女')),
StuBirthday date ,
StuPhone nvarchar(11) unique
)
create table Course
(
CourseID int primary key identity(1,1),
CourseName nvarchar(50) unique not null,
CourseCredit int check(CourseCredit>=1 and CourseCredit<=5) default(1) not null,
)
create table Score
(
ScoreID int identity(1,1),
StuID int ,
CourseID int ,
Score  decimal(5,2) unique not null
)
alter table Student add StuAddress nvarchar(200)
alter table Score add constraint PK_Score_ScoreID primary key(ScoreID)
alter table Score add constraint FK_Score_StuID foreign key(StuID) references Student(StuID)
alter table Score add constraint FK_Score_CourseID foreign key(CourseID) references Course(CourseID)